package com.zhc.dejingapi.pojo.qingflow.downdata;

import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Data
public class QingDownDataMainDBModel implements Serializable {
    private static final Logger log = LoggerFactory.getLogger(QingDownDataMainDBModel.class);

    public QingDownDataMainDBModel(){

    }
    public QingDownDataMainDBModel(String strAppKey, JdbcTemplate mysqlJDBC){

        appKey=strAppKey;
        String strSql="SELECT a.appKey,a.appMainId,main_id ,a.appType,a.mainTableName,a.isNeedSyn\n" +
                "from qing_downdatamain a \n" +
                "where a.appKey=? and a.isNeedSyn=1 ";
        Map<String ,Object> mapResult=mysqlJDBC.queryForMap(strSql,strAppKey);
        if(mapResult==null||mapResult.isEmpty()){

        }
        else{
            Object  objVal=mapResult.get("appMainId");
            //region appMainId
            if (objVal==null||objVal.toString().isEmpty()){

            }
            else{
                try {
                 appMainId=   Long.parseLong(objVal.toString());
                }
                catch (Exception e){

                }
            }
            //endregion

            //region main_id
            objVal=mapResult.get("main_id");
            if (objVal==null||objVal.toString().isEmpty()){

            }
            else{
                try {
                    main_id=   Long.parseLong(objVal.toString());
                }
                catch (Exception e){

                    e.printStackTrace();
                }
            }
            //endregion

            //region appType
            objVal=mapResult.get("appType");
            if (objVal==null||objVal.toString().isEmpty()){

            }
            else{
                try {
                    appType=   Integer.parseInt(objVal.toString());
                }
                catch (Exception e){
                    e.printStackTrace();

                }
            }
            //endregion

            //region isNeedSyn
            objVal=mapResult.get("isNeedSyn");
            if (objVal==null||objVal.toString().isEmpty()){

            }
            else{
                try {
                    isNeedSyn=   Integer.parseInt(objVal.toString());
                }
                catch (Exception e){

                    e.printStackTrace();
                }
            }
            //endregion

            //region mainTableName
            objVal=mapResult.get("mainTableName");
            if (objVal==null||objVal.toString().isEmpty()){

            }
            else{
                try {
                    mainTableName= objVal.toString();
                }
                catch (Exception e){
                    e.printStackTrace();
                }
            }
            //endregion

            //region mainTableName
//            objVal=mapResult.get("mainTableName");
//            if (objVal==null||objVal.toString().isEmpty()){
//
//            }
//            else{
//                try {
//                    mainTableName= objVal.toString();
//                }
//                catch (Exception e){
//
//                }
//            }
            //endregion
        }


    }

    public void loadMainFields(JdbcTemplate mysqlJDBC){
        String strSql="SELECT a.details_id,a.detail_id,a.main_id,\n" +
                "       a.tableName,a.qingTitle,a.dbFieldName,a.dbFieldType\n" +
                "from qing_donwdatafields a\n" +
                "LEFT JOIN qing_downdatamain b on a.main_id=b.main_id\n" +
                " where b.appMainId=? \n" +
                "and ( a.detail_id is NULL or a.detail_id=0)";
        listFields=mysqlJDBC.query(strSql,new BeanPropertyRowMapper<>(QingDownDataFieldDBModel.class),appMainId);

        if(listFields==null||listFields.isEmpty()){

        }
        else{
            mapFieldValue=new HashMap<>();
            mapFieldContrast=new HashMap<>();
            mapFieldType=new HashMap<>();
            strFieldNameBuilder=new StringBuilder();
            strFieldParaBuilder=new StringBuilder();
            strFieldParaATBuilder=new StringBuilder();
            strFieldUpdateBuilder=new StringBuilder();
            //strFieldList="qingApplyId";
            strFieldNameBuilder.append("qingApplyId");
            strFieldParaATBuilder.append("@qingApplyId");
            strFieldUpdateBuilder.append("qingApplyId=?");
            //strFieldPara="?";
            strFieldParaBuilder.append("?");

            //strFieldList="qingApplyId";
            strFieldNameBuilder.append(",downBatchId");
            //strFieldPara="?";
            strFieldParaBuilder.append(",?");
            strFieldParaATBuilder.append(",@downBatchId");
            strFieldUpdateBuilder.append(",downBatchId=?");

            if(mapDefaultFieldValue==null||mapDefaultFieldValue.isEmpty()){


            }
            else{
                for (String strKey : mapDefaultFieldValue.keySet()){
                    strFieldNameBuilder.append(","+strKey);
                    strFieldParaBuilder.append(",?");
                    strFieldParaATBuilder.append(",@"+strKey);
                    strFieldUpdateBuilder.append(","+strKey+"=?");
                }
            }
            for (int i = 0; i < listFields.size(); i++) {
                mapFieldContrast.put(listFields.get(i).getQingTitle(), listFields.get(i).getDbFieldName());
                //strFieldList += "," + listFields.get(i).getDbFieldName();
                strFieldNameBuilder.append("," + listFields.get(i).getDbFieldName());
                //strFieldPara += ",?";
                strFieldParaBuilder.append(",?");
                strFieldParaATBuilder.append(",@"+listFields.get(i).getDbFieldName());
                strFieldUpdateBuilder.append("," + listFields.get(i).getDbFieldName() + "=?");
                mapFieldValue.put(listFields.get(i).getDbFieldName(), null);
                mapFieldType.put(listFields.get(i).getDbFieldName(), listFields.get(i).getDbFieldType());
            }
        }
    }
    public void loadDetailItems(JdbcTemplate mysqlJDBC){

        String strSql="SELECT detail_id,main_id,\n" +
                "       mainTblFieldName,detailTableName,\n" +
                "\t\t\t isNeedSyn\n" +
                "FROM qing_downdatadetail\n" +
                "where main_id=?";
        listDetails=mysqlJDBC.query(strSql,new BeanPropertyRowMapper<>(QingDownDataDetailDBModel.class),main_id);

        if(listDetails==null||listDetails.isEmpty()){

        }
        else {

            mapDetails=new HashMap<>();
            for (int i = 0; i < listDetails.size(); i++) {
                listDetails.get(i).loadMainFields(mysqlJDBC);
                mapDetails.put(listDetails.get(i).getMainTblFieldName(),listDetails.get(i));
            }
        }
    }

    //名	类型	长度
    //main_id	bigint
    private long main_id;
    //appMainId	bigint
    private long appMainId;
    //appKey	varchar
    private String appKey;
    //appType	int
    private int appType;
    //mainTableName	varchar	80
    private String mainTableName;

    //isNeedSyn	int
    private int isNeedSyn;

    private List<QingDownDataFieldDBModel> listFields;

    private List<QingDownDataDetailDBModel> listDetails;


    @Getter
    @Setter
    private Long applyId;

    @Getter
    @Setter
    private String downBatchId="";//保留，以达到能用要求

    @Getter
    @Setter
    private Map<String,Object > mapFieldValue=null;

    @Getter
    @Setter
    private Map<String,Integer > mapFieldType=null;

    @Setter
    @Getter
    private Map<String,String > mapFieldContrast=null;

    private Map<String,String> mapDefaultFieldValue=null;
    private Map<String,QingDownDataDetailDBModel> mapDetails=null;

    //private String strFieldList="qingApplyId";
    private StringBuilder strFieldNameBuilder=new StringBuilder();
    //private String strFieldPara="?";
    private StringBuilder strFieldParaBuilder=new StringBuilder();
    //private List<Object> ListParameter=null;
    private StringBuilder strFieldParaATBuilder=new StringBuilder();

    private StringBuilder strFieldUpdateBuilder=new StringBuilder();



    public void PutFieldValue(String strQueTitle,String strFieldValue) throws Exception{

        if(mapFieldContrast.containsKey(strQueTitle)){
            mapFieldValue.put(mapFieldContrast.get(strQueTitle),strFieldValue);
        }
        else{

            throw new Exception("主表字段["+strQueTitle+"]不存在！");
        }
    }

    public Boolean CheckExistsDetailQueTitle(String strQueTitle){
        if (mapDetails==null||mapDetails.isEmpty()){
            return false;
        }
        else{
            return mapDetails.containsKey(strQueTitle);
        }
    }

    public void PutDetailFieldValue(Integer iRowIndex, String strMainQueTitle, String strQueTitle,String strFieldValue) throws Exception{

        if (mapDetails==null||mapDetails.isEmpty()){
            throw new Exception("当前表无明细记录！");
        }
        mapDetails.get(strMainQueTitle).PutFieldValue(iRowIndex, strQueTitle,strFieldValue);

    }
    public void PutDetailSelFieldValue(Integer iRowIndex, String strMainQueTitle, String strQueTitle,String strFieldValue) throws Exception{

        if (mapDetails==null||mapDetails.isEmpty()){
            throw new Exception("当前表无明细记录！");
        }
        mapDetails.get(strMainQueTitle).PutSelFieldValue(iRowIndex, strQueTitle,strFieldValue);

    }
    /**
     * 保存到数据库
     * @param mysqlJDBC
     */
    public void SaveToDB(JdbcTemplate mysqlJDBC){

       // String strSqlCount="select count(*) from "+mainTableName+" where qingApplyId=? and downBatchId=?";
        //int iHaveCount=mysqlJDBC.queryForObject(strSqlCount,new Object[]{applyId,downBatchId},Integer.class);
        //if(iHaveCount>0){
        //    UdpDataToDB(mysqlJDBC);
       // }
        //else{
            AddDataToDB(mysqlJDBC);
        //}

    }
    public void SaveLastToDB(JdbcTemplate mysqlJDBC){

        String strSqlCount="select count(*) from "+mainTableName+" where qingApplyId=? ";
        int iHaveCount=mysqlJDBC.queryForObject(strSqlCount,new Object[]{applyId},Integer.class);
        if(iHaveCount>0){
            UdpDataToDB(mysqlJDBC);
        }
        else{
            AddDataToDB(mysqlJDBC);
        }

    }

    private void AddDataToDB(JdbcTemplate mysqlJDBC){

        //保存到数据库
        //先保存主表
        //mysqlJDBC.getDataSource();


        // 创建DataSourceTransactionManager
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(mysqlJDBC.getDataSource());

        // 创建TransactionTemplate
        TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);

        // 定义事务的内容
        transactionTemplate.execute((TransactionCallback<Void>) transactionStatus -> {
            // 在这里执行数据库操作
            //jdbcTemplate.update("INSERT INTO your_table (column1, column2) VALUES (?, ?)", value1, value2);
            //jdbcTemplate.update("UPDATE your_table SET column1 = ? WHERE id = ?", newValue, id);

            final List<Object> ListParameter=new ArrayList<>();
            try{


                //region 保存主表
                //生成SQL语句


                    //region 新增模式
                   String  strSqlDB="insert into "+mainTableName+"("+strFieldNameBuilder.toString()+") \n "
                            +"values("+strFieldParaBuilder.toString()+")";

                    ListParameter.add(applyId);
                    ListParameter.add(downBatchId);
                if(mapDefaultFieldValue==null||mapDefaultFieldValue.isEmpty()){

                    strSqlDB="insert into "+mainTableName+"("+strFieldNameBuilder.toString()+") \n "
                            +"values("+strFieldParaBuilder.toString()+")";
                }
                else{
                    strSqlDB="insert into "+mainTableName+"("+strFieldNameBuilder.toString()+") \n "
                            +"values("+strFieldParaBuilder.toString()+")";
                    for (String strKey : mapDefaultFieldValue.keySet()){
                        //strFieldNameBuilder.append(","+strKey);
                        //strFieldParaBuilder.append(",?");
                        //strFieldParaATBuilder.append(",@"+strKey);
                        //strFieldUpdateBuilder.append(","+strKey+"=?");
                        ListParameter.add(mapDefaultFieldValue.get(strKey));
                    }
                }
                    for (int i = 0; i < listFields.size(); i++) {
                        QingDownDataFieldDBModel dataFieldDBModel=listFields.get(i);

                        QingDownDataFieldDBModel fieldDBModel=listFields.get(i);

                        if(mapFieldValue.containsKey(fieldDBModel.getDbFieldName())){
                            Object objValue= mapFieldValue.get( fieldDBModel.getDbFieldName());
                            ListParameter.add( objValue);
                        }
                        else{
                            ListParameter.add(null);
                        }

                    }
                    //endregion


                //final   String strSql="insert into "+mainTableName+"("+strFieldNameBuilder.toString()+") \n "
                //       +"values("+strFieldParaBuilder.toString()+")";

                final   String strSql=strSqlDB;


                KeyHolder keyHolder = new GeneratedKeyHolder();

                mysqlJDBC.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                        PreparedStatement ps  = con.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);

                        for (int i = 0; i < ListParameter.size(); i++) {
                            ps.setObject(i+1, ListParameter.get(i));
                        }


                        return ps;
                    }
                },keyHolder);
                //endregion

                Long lMainId=keyHolder.getKey().longValue();

                if (listDetails==null||listDetails.isEmpty()){
                    //无子表，不需要

                }
                else {
                    for (int i = 0; i < listDetails.size(); i++) {

                        QingDownDataDetailDBModel detailDBModel=listDetails.get(i);
                        //detailDBModel.SaveToDB(mysqlJDBC,lMainId,applyId);

                        Map<Integer,Map<String,Object>> mapRowFieldValue=detailDBModel.getMapRowFieldValue();

                        for(Integer key:mapRowFieldValue.keySet()){

                            Map<String,Object> mapFieldItemValues=mapRowFieldValue.get(key);

                            //生成SQL语句
                            String strSqlDetail="insert into "+detailDBModel.getDetailTableName()+"("+detailDBModel.getStrFieldNameBuilder().toString()+") \n ";
                            strSqlDetail+="values("+detailDBModel.getStrFieldParaBuilder().toString()+")";
                            List<Object> ListParameterDetail=new ArrayList<>();
                            ListParameterDetail.add(lMainId);
                            ListParameterDetail.add(applyId);

                            for (int iRowIndex = 0; iRowIndex < detailDBModel.getListFields().size(); iRowIndex++) {
                                //QingDownDataFieldDBModel fieldDBModel=listFields.get(i);
                                //ListParameter.add(  mapFieldValue.get(  fieldDBModel.getQingTitle()));
                                QingDownDataFieldDBModel fieldDBModel = detailDBModel.getListFields().get(iRowIndex);

                                if(mapFieldItemValues.containsKey(fieldDBModel.getDbFieldName())){
                                    Object objValue = mapFieldItemValues.get(fieldDBModel.getDbFieldName());
                                    ListParameterDetail.add(objValue);
                                }
                                else{
                                    ListParameterDetail.add(null);
                                }
                            }

                            mysqlJDBC.update(strSqlDetail,ListParameterDetail.toArray());
                        }


                        detailDBModel.setMapRowFieldValue(new HashMap<>());
                        //mapRowFieldValue=new HashMap<>();
                        //mapFieldValue=new HashMap<>();
                        detailDBModel.setMapFieldValue(new HashMap<>());
                    }
                }
                mapFieldValue=new HashMap<>();
            }
            catch (Exception e){
                e.printStackTrace();

                throw e;
            }

            // 如果需要回滚事务，可以在这里抛出异常
            // 如果方法正常结束，事务将被提交
            return null;
        });


    }
    private void UdpDataToDB(JdbcTemplate mysqlJDBC) {

        if (!(downBatchId == null || downBatchId.isEmpty())) {
            //有批次模式不需要更新
            return;
        }
        String strSqlGet="select main_id from "+mainTableName+" where qingApplyId=?";


        Long lMainId=mysqlJDBC.queryForObject(strSqlGet, Long.class, applyId);

        //保存到数据库
        //先保存主表
        //mysqlJDBC.getDataSource();


        // 创建DataSourceTransactionManager
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(mysqlJDBC.getDataSource());

        // 创建TransactionTemplate
        TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);

        // 定义事务的内容
        transactionTemplate.execute((TransactionCallback<Void>) transactionStatus -> {
            // 在这里执行数据库操作
            //jdbcTemplate.update("INSERT INTO your_table (column1, column2) VALUES (?, ?)", value1, value2);
            //jdbcTemplate.update("UPDATE your_table SET column1 = ? WHERE id = ?", newValue, id);


            final List<Object> ListParameter = new ArrayList<>();
            try {


                //region 保存主表
                //生成SQL语句

                String strSqlDB = "";
                for (int i = 0; i < listDetails.size(); i++) {
                    strSqlDB="delete from "+listDetails.get(i).getDetailTableName()+" where main_id=? ";
                    mysqlJDBC.update(strSqlDB,lMainId);
                }


                //region 修改模式
                strSqlDB = "update " + mainTableName + " set \n " + strFieldUpdateBuilder.toString() + " \n "
                        + " where qingApplyId=?";

                ListParameter.add(applyId);
                ListParameter.add(downBatchId);
                if(mapDefaultFieldValue==null||mapDefaultFieldValue.isEmpty()){


                }
                else{
                    for (String strKey : mapDefaultFieldValue.keySet()){
                        //strFieldNameBuilder.append(","+strKey);
                        //strFieldParaBuilder.append(",?");
                        //strFieldParaATBuilder.append(",@"+strKey);
                        //strFieldUpdateBuilder.append(","+strKey+"=?");
                        ListParameter.add(mapDefaultFieldValue.get(strKey));
                    }
                }
                for (int i = 0; i < listFields.size(); i++) {
                    QingDownDataFieldDBModel dataFieldDBModel = listFields.get(i);

                    QingDownDataFieldDBModel fieldDBModel = listFields.get(i);

                    if (mapFieldValue.containsKey(fieldDBModel.getDbFieldName())) {
                        Object objValue = mapFieldValue.get(fieldDBModel.getDbFieldName());
                        ListParameter.add(objValue);
                    } else {
                        ListParameter.add(null);
                    }

                }
                ListParameter.add(applyId);
                //endregion


                //final   String strSql="insert into "+mainTableName+"("+strFieldNameBuilder.toString()+") \n "
                //       +"values("+strFieldParaBuilder.toString()+")";

                final String strSql = strSqlDB;

                mysqlJDBC.update(strSqlDB, ListParameter.toArray());

                //endregion


                if (listDetails == null || listDetails.isEmpty()) {
                    //无子表，不需要

                } else {
                    for (int i = 0; i < listDetails.size(); i++) {

                        QingDownDataDetailDBModel detailDBModel = listDetails.get(i);
                        //detailDBModel.SaveToDB(mysqlJDBC,lMainId,applyId);

                        Map<Integer, Map<String, Object>> mapRowFieldValue = detailDBModel.getMapRowFieldValue();

                        for (Integer key : mapRowFieldValue.keySet()) {

                            Map<String, Object> mapFieldItemValues = mapRowFieldValue.get(key);

                            //生成SQL语句
                            String strSqlDetail = "insert into " + detailDBModel.getDetailTableName() + "(" + detailDBModel.getStrFieldNameBuilder().toString() + ") \n ";
                            strSqlDetail += "values(" + detailDBModel.getStrFieldParaBuilder().toString() + ")";
                            List<Object> ListParameterDetail = new ArrayList<>();
                            ListParameterDetail.add(lMainId);
                            ListParameterDetail.add(applyId);

                            for (int iRowIndex = 0; iRowIndex < detailDBModel.getListFields().size(); iRowIndex++) {
                                //QingDownDataFieldDBModel fieldDBModel=listFields.get(i);
                                //ListParameter.add(  mapFieldValue.get(  fieldDBModel.getQingTitle()));
                                QingDownDataFieldDBModel fieldDBModel = detailDBModel.getListFields().get(iRowIndex);

                                if (mapFieldItemValues.containsKey(fieldDBModel.getDbFieldName())) {
                                    Object objValue = mapFieldItemValues.get(fieldDBModel.getDbFieldName());
                                    ListParameterDetail.add(objValue);
                                } else {
                                    ListParameterDetail.add(null);
                                }
                            }

                            mysqlJDBC.update(strSqlDetail, ListParameterDetail.toArray());
                        }


                        detailDBModel.setMapRowFieldValue(new HashMap<>());
                        //mapRowFieldValue=new HashMap<>();
                        //mapFieldValue=new HashMap<>();
                        detailDBModel.setMapFieldValue(new HashMap<>());
                    }
                }
                mapFieldValue = new HashMap<>();
            } catch (Exception e) {
                e.printStackTrace();
                throw e;
            }

            // 如果需要回滚事务，可以在这里抛出异常
            // 如果方法正常结束，事务将被提交
            return null;
        });


    }

    public void SaveSelectToDB(JdbcTemplate mysqlJDBC){

        try {

            String strSqlCount = "select count(*) from " + mainTableName + " where qingApplyId=? ";
            int iHaveCount = 0;
            if (mapDefaultFieldValue == null || mapDefaultFieldValue.isEmpty()) {
                strSqlCount = "select count(*) from " + mainTableName + " where qingApplyId=? ";
                iHaveCount = mysqlJDBC.queryForObject(strSqlCount, new Object[]{applyId}, Integer.class);
            } else {
                strSqlCount = "select count(*) from " + mainTableName + " where qingApplyId=? ";
                List<Object> listValue = new ArrayList<>();
                listValue.add(applyId);
                for (String strKey : mapDefaultFieldValue.keySet()) {
                    //strFieldNameBuilder.append(","+strKey);
                    //strFieldParaBuilder.append(",?");
                    //strFieldParaATBuilder.append(",@"+strKey);
                    //strFieldUpdateBuilder.append(","+strKey+"=?");
                    //ListParameter.add(mapDefaultFieldValue.get(strKey));
                    strSqlCount += " and " + strKey + "=? ";
                    listValue.add(mapDefaultFieldValue.get(strKey));
                }
                iHaveCount = mysqlJDBC.queryForObject(strSqlCount, listValue.toArray(), Integer.class);
            }
            //int iHaveCount=mysqlJDBC.queryForObject(strSqlCount,new Object[]{applyId},Integer.class);
            if (iHaveCount > 0) {
                UdpSelectDataToDB(mysqlJDBC);
            } else {
                AddSelectDataToDB(mysqlJDBC);
            }
        }
        catch (Exception e){
            e.printStackTrace();
            log.error(e.getMessage());
        }
    }

    private void AddSelectDataToDB(JdbcTemplate mysqlJDBC){

        //保存到数据库
        //先保存主表
        //mysqlJDBC.getDataSource();


        // 创建DataSourceTransactionManager
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(mysqlJDBC.getDataSource());

        // 创建TransactionTemplate
        TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);

        // 定义事务的内容
        transactionTemplate.execute((TransactionCallback<Void>) transactionStatus -> {
            // 在这里执行数据库操作
            //jdbcTemplate.update("INSERT INTO your_table (column1, column2) VALUES (?, ?)", value1, value2);
            //jdbcTemplate.update("UPDATE your_table SET column1 = ? WHERE id = ?", newValue, id);

            final List<Object> ListParameter=new ArrayList<>();
            try{


                //region 保存主表
                //生成SQL语句


                //region 新增模式
                String  strSqlDB="insert into "+mainTableName+"("+strFieldNameBuilder.toString()+") \n "
                        +"values("+strFieldParaBuilder.toString()+")";

                ListParameter.add(applyId);
                ListParameter.add(downBatchId);
                if(mapDefaultFieldValue==null||mapDefaultFieldValue.isEmpty()){
                }
                else{
                    for (String strKey : mapDefaultFieldValue.keySet()){
                        //strFieldNameBuilder.append(","+strKey);
                        //strFieldParaBuilder.append(",?");
                        //strFieldParaATBuilder.append(",@"+strKey);
                        //strFieldUpdateBuilder.append(","+strKey+"=?");
                        ListParameter.add(mapDefaultFieldValue.get(strKey));
                    }
                }
                for (int i = 0; i < listFields.size(); i++) {
                    QingDownDataFieldDBModel dataFieldDBModel=listFields.get(i);

                    QingDownDataFieldDBModel fieldDBModel=listFields.get(i);

                    if(mapFieldValue.containsKey(fieldDBModel.getDbFieldName())){
                        Object objValue= mapFieldValue.get( fieldDBModel.getDbFieldName());
                        ListParameter.add( objValue);
                    }
                    else{
                        ListParameter.add(null);
                    }

                }
                //endregion


                //final   String strSql="insert into "+mainTableName+"("+strFieldNameBuilder.toString()+") \n "
                //       +"values("+strFieldParaBuilder.toString()+")";

                final   String strSql=strSqlDB;


                KeyHolder keyHolder = new GeneratedKeyHolder();

                mysqlJDBC.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                        PreparedStatement ps  = con.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);

                        for (int i = 0; i < ListParameter.size(); i++) {
                            ps.setObject(i+1, ListParameter.get(i));
                        }


                        return ps;
                    }
                },keyHolder);
                //endregion

                Long lMainId=keyHolder.getKey().longValue();

                if (listDetails==null||listDetails.isEmpty()){
                    //无子表，不需要

                }
                else {
                    for (int i = 0; i < listDetails.size(); i++) {

                        QingDownDataDetailDBModel detailDBModel=listDetails.get(i);
                        //detailDBModel.SaveToDB(mysqlJDBC,lMainId,applyId);

                        Map<Integer,Map<String,Object>> mapRowFieldValue=detailDBModel.getMapRowFieldValue();

                        for(Integer key:mapRowFieldValue.keySet()){

                            Map<String,Object> mapFieldItemValues=mapRowFieldValue.get(key);

                            //生成SQL语句
                            String strSqlDetail="insert into "+detailDBModel.getDetailTableName()+"("+detailDBModel.getStrFieldNameBuilder().toString()+") \n ";
                            strSqlDetail+="values("+detailDBModel.getStrFieldParaBuilder().toString()+")";
                            List<Object> ListParameterDetail=new ArrayList<>();
                            ListParameterDetail.add(lMainId);
                            ListParameterDetail.add(applyId);

                            for (int iRowIndex = 0; iRowIndex < detailDBModel.getListFields().size(); iRowIndex++) {
                                //QingDownDataFieldDBModel fieldDBModel=listFields.get(i);
                                //ListParameter.add(  mapFieldValue.get(  fieldDBModel.getQingTitle()));
                                QingDownDataFieldDBModel fieldDBModel = detailDBModel.getListFields().get(iRowIndex);

                                if(mapFieldItemValues.containsKey(fieldDBModel.getDbFieldName())){
                                    Object objValue = mapFieldItemValues.get(fieldDBModel.getDbFieldName());
                                    ListParameterDetail.add(objValue);
                                }
                                else{
                                    ListParameterDetail.add(null);
                                }
                            }

                            mysqlJDBC.update(strSqlDetail,ListParameterDetail.toArray());
                        }


                        detailDBModel.setMapRowFieldValue(new HashMap<>());
                        //mapRowFieldValue=new HashMap<>();
                        //mapFieldValue=new HashMap<>();
                        detailDBModel.setMapFieldValue(new HashMap<>());
                    }
                }
                mapFieldValue=new HashMap<>();
            }
            catch (Exception e){
                e.printStackTrace();

                throw e;
            }

            // 如果需要回滚事务，可以在这里抛出异常
            // 如果方法正常结束，事务将被提交
            return null;
        });


    }

    private void UdpSelectDataToDB(JdbcTemplate mysqlJDBC) {

        if (!(downBatchId == null || downBatchId.isEmpty())) {
            //有批次模式不需要更新
            return;
        }



        String strSqlGet="select main_id from "+mainTableName+" where qingApplyId=?";
        Long lMainId=0l;
        //Long lMainId=mysqlJDBC.queryForObject(strSqlGet, Long.class, applyId);
        if(mapDefaultFieldValue==null||mapDefaultFieldValue.isEmpty()){

            lMainId=mysqlJDBC.queryForObject(strSqlGet, Long.class, applyId);
        }
        else{
            List<Object> listValue=new ArrayList<>();
            listValue.add(applyId);
            for (String strKey : mapDefaultFieldValue.keySet()) {

                strSqlGet += " and " + strKey + "=? ";
                listValue.add(mapDefaultFieldValue.get(strKey));

            }
            lMainId=mysqlJDBC.queryForObject(strSqlGet, listValue.toArray(), Long.class);

        }
        final Long lMainIdUse=lMainId;
        //保存到数据库
        //先保存主表
        //mysqlJDBC.getDataSource();


        // 创建DataSourceTransactionManager
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(mysqlJDBC.getDataSource());

        // 创建TransactionTemplate
        TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);

        // 定义事务的内容
        transactionTemplate.execute((TransactionCallback<Void>) transactionStatus -> {
            // 在这里执行数据库操作
            //jdbcTemplate.update("INSERT INTO your_table (column1, column2) VALUES (?, ?)", value1, value2);
            //jdbcTemplate.update("UPDATE your_table SET column1 = ? WHERE id = ?", newValue, id);

            final List<Object> ListParameter = new ArrayList<>();
            try {

                //region 保存主表
                //生成SQL语句

                String strSqlDB = "";
                for (int i = 0; i < listDetails.size(); i++) {
                    strSqlDB="delete from "+listDetails.get(i).getDetailTableName()+" where main_id=? ";
                    mysqlJDBC.update(strSqlDB,lMainIdUse);
                }

                //region 修改模式
                strSqlDB = "update " + mainTableName + " set \n " + strFieldUpdateBuilder.toString() + " \n "
                        + " where main_id=?";

                ListParameter.add(applyId);
                ListParameter.add(downBatchId);
                if(mapDefaultFieldValue==null||mapDefaultFieldValue.isEmpty()){

                }
                else{
                    for (String strKey : mapDefaultFieldValue.keySet()){

                        ListParameter.add(mapDefaultFieldValue.get(strKey));
                    }
                }
                for (int i = 0; i < listFields.size(); i++) {
                    QingDownDataFieldDBModel dataFieldDBModel = listFields.get(i);

                    QingDownDataFieldDBModel fieldDBModel = listFields.get(i);

                    if (mapFieldValue.containsKey(fieldDBModel.getDbFieldName())) {
                        Object objValue = mapFieldValue.get(fieldDBModel.getDbFieldName());
                        ListParameter.add(objValue);
                    } else {
                        ListParameter.add(null);
                    }

                }
                ListParameter.add(lMainIdUse);
                //endregion

                //final   String strSql="insert into "+mainTableName+"("+strFieldNameBuilder.toString()+") \n "
                //       +"values("+strFieldParaBuilder.toString()+")";

                final String strSql = strSqlDB;

                mysqlJDBC.update(strSqlDB, ListParameter.toArray());

                //endregion

                if (listDetails == null || listDetails.isEmpty()) {
                    //无子表，不需要

                } else {
                    for (int i = 0; i < listDetails.size(); i++) {

                        QingDownDataDetailDBModel detailDBModel = listDetails.get(i);
                        //detailDBModel.SaveToDB(mysqlJDBC,lMainId,applyId);

                        Map<Integer, Map<String, Object>> mapRowFieldValue = detailDBModel.getMapRowFieldValue();

                        for (Integer key : mapRowFieldValue.keySet()) {

                            Map<String, Object> mapFieldItemValues = mapRowFieldValue.get(key);

                            //生成SQL语句
                            String strSqlDetail = "insert into " + detailDBModel.getDetailTableName() + "(" + detailDBModel.getStrFieldNameBuilder().toString() + ") \n ";
                            strSqlDetail += "values(" + detailDBModel.getStrFieldParaBuilder().toString() + ")";
                            List<Object> ListParameterDetail = new ArrayList<>();
                            ListParameterDetail.add(lMainIdUse);
                            ListParameterDetail.add(applyId);

                            for (int iRowIndex = 0; iRowIndex < detailDBModel.getListFields().size(); iRowIndex++) {
                                //QingDownDataFieldDBModel fieldDBModel=listFields.get(i);
                                //ListParameter.add(  mapFieldValue.get(  fieldDBModel.getQingTitle()));
                                QingDownDataFieldDBModel fieldDBModel = detailDBModel.getListFields().get(iRowIndex);

                                if (mapFieldItemValues.containsKey(fieldDBModel.getDbFieldName())) {
                                    Object objValue = mapFieldItemValues.get(fieldDBModel.getDbFieldName());
                                    ListParameterDetail.add(objValue);
                                } else {
                                    ListParameterDetail.add(null);
                                }
                            }

                            mysqlJDBC.update(strSqlDetail, ListParameterDetail.toArray());
                        }

                        detailDBModel.setMapRowFieldValue(new HashMap<>());
                        //mapRowFieldValue=new HashMap<>();
                        //mapFieldValue=new HashMap<>();
                        detailDBModel.setMapFieldValue(new HashMap<>());
                    }
                }
                mapFieldValue = new HashMap<>();
            } catch (Exception e) {
                e.printStackTrace();

                throw e;
            }

            // 如果需要回滚事务，可以在这里抛出异常
            // 如果方法正常结束，事务将被提交
            return null;
        });


    }

}
